/*
CREATE TABLE NSD.WEBSITE_COMPANY_REQUESTS_V2 (
    EMAIL_ADDRESS            VARCHAR(50),
    COMPANY_DETAILS           VARCHAR(1000),
    REPORT_TITLES_ID         INT,
    PRIMARY KEY (EMAIL_ADDRESS,REPORT_TITLES_ID)
);
*/
package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class WebsiteCompanyRequestsDAO implements java.io.Serializable{

	Connection con;

	public WebsiteCompanyRequestsDAO(Connection connection){
		con = connection;
	}

	public int addWebsiteCompanyRequests(String emailAddress,String companyDetails,int reportTitlesId)throws SQLException{
		int updateCols = 0;
		// if the company string is empty, then delete the records
		if(companyDetails == null || companyDetails.equals("")){
			PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_COMPANY_REQUESTS_V2 WHERE EMAIL_ADDRESS = ? AND REPORT_TITLES_ID = ?");
			pstmt.setString(1,emailAddress);
			pstmt.setInt(2,reportTitlesId);

			updateCols = pstmt.executeUpdate();
			pstmt.close();
		}else{
			// first try to update any existing record, otherwise add the new record.
			PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_COMPANY_REQUESTS_V2 SET COMPANY_DETAILS = ? WHERE EMAIL_ADDRESS = ? AND REPORT_TITLES_ID = ?");
			pstmt.setString(1,companyDetails);
			pstmt.setString(2,emailAddress);
			pstmt.setInt(3,reportTitlesId);

			updateCols = pstmt.executeUpdate();
			pstmt.close();
			if(updateCols == 0){
				pstmt = con.prepareStatement("INSERT INTO WEBSITE_COMPANY_REQUESTS_V2(EMAIL_ADDRESS,COMPANY_DETAILS,REPORT_TITLES_ID)VALUES(?,?,?)");
				pstmt.setString(1,emailAddress);
				pstmt.setString(2,companyDetails);
				pstmt.setInt(3,reportTitlesId);
				updateCols = pstmt.executeUpdate();
				pstmt.close();
			}
		}
		return updateCols;
	}

	public String selectWebsiteCompanyRequestsByPK(String emailAddress,int reportTitlesId)throws SQLException{

		String strCompanyIDs = null;
		PreparedStatement pstmt = con.prepareStatement("SELECT COMPANY_DETAILS FROM WEBSITE_COMPANY_REQUESTS_V2 WHERE EMAIL_ADDRESS = ? AND REPORT_TITLES_ID = ?");
		pstmt.setString(1,emailAddress);
		pstmt.setInt(2,reportTitlesId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			strCompanyIDs = rs.getString("COMPANY_DETAILS");
		}
		rs.close();
		pstmt.close();
		return strCompanyIDs;
	}

	/*
	public DbResultSet selectWebsiteCompanyRequestsRecords(String whereClause,String orderByClause)throws SQLException{

		ArrayList records = new ArrayList();
		String sql ="SELECT WEBSITE_COMPANY_REQUESTS_ID,EMAIL_ADDRESS,COMPANY_NUMBER,REPORT_TITLES_ID FROM WEBSITE_COMPANY_REQUESTS";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			WebsiteCompanyRequestsBean bn = new WebsiteCompanyRequestsBean(rs.getInt("WEBSITE_COMPANY_REQUESTS_ID"),rs.getString("EMAIL_ADDRESS"),rs.getString("COMPANY_NUMBER"),rs.getInt("REPORT_TITLES_ID"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		returnConnection(con);
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}



	public int updateWebsiteCompanyRequests(WebsiteCompanyRequestsBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_COMPANY_REQUESTS SET EMAIL_ADDRESS = ? ,COMPANY_NUMBER = ? ,REPORT_TITLES_ID = ?  WHERE WEBSITE_COMPANY_REQUESTS_ID = ?");
		pstmt.setString(1,bean.getEmailAddress());
		pstmt.setString(2,bean.getCompanyNumber());
		pstmt.setInt(3,bean.getReportTitlesId());
		pstmt.setInt(4,bean.getWebsiteCompanyRequestsId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}

	public int deleteWebsiteCompanyRequests(WebsiteCompanyRequestsBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_COMPANY_REQUESTS WHERE WEBSITE_COMPANY_REQUESTS_ID = ?");
		pstmt.setInt(1,bean.getWebsiteCompanyRequestsId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}
	*/
}